CREATE TABLE [dbo].[Category](
	[CategoryId] [int] IDENTITY(1,1) NOT NULL,
	[CategoryName] [varchar](250) NOT NULL,
	[ParentId] [int] NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[CategoryId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Category]  WITH CHECK ADD  CONSTRAINT [FK_Category_Category] FOREIGN KEY([ParentId])
REFERENCES [dbo].[Category] ([CategoryId])
GO
ALTER TABLE [dbo].[Category] CHECK CONSTRAINT [FK_Category_Category]
GO
CREATE PROCEDURE [dbo].[spCategory_Add]
(
	@CategoryName VARCHAR(250),
	@ParentId INT = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	INSERT INTO Category
	(CategoryName, ParentId)
	VALUES (@CategoryName, @ParentId)
END
GO
CREATE PROCEDURE [dbo].[spCategory_GetList]
AS
BEGIN
	SET NOCOUNT ON

	SELECT *
	FROM Category (nolock)
END
GO
CREATE PROCEDURE [dbo].[spCategory_Delete]
(
	@CategoryId INT
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	DECLARE @MaxLevel INT
	
	CREATE TABLE #CategoryLevelList
	(
		CategoryId INT,
		CategoryLevel INT
	);
	
	WITH CategoryLevelList
	AS
	(
		SELECT CategoryId, ParentId, CategoryName, 1 AS [Level]
		FROM Category
		WHERE CategoryId = @CategoryId
	
		UNION ALL

		SELECT c.CategoryId, c.ParentId, c.CategoryName, cll.[Level] + 1 AS Level
		FROM Category AS c INNER JOIN CategoryLevelList AS cll ON c.ParentId = cll.CategoryId
	)

	
	INSERT INTO #CategoryLevelList
	(CategoryId, CategoryLevel)
	SELECT CategoryId, [Level]
	FROM CategoryLevelList
	
	SELECT @MaxLevel = MAX(CategoryLevel)
	FROM #CategoryLevelList
	
	BEGIN TRANSACTION
	
		WHILE @MaxLevel >= 1
		BEGIN
			DELETE 
			FROM Category
			WHERE EXISTS(SELECT 1 FROM #CategoryLevelList AS cll WHERE cll.CategoryId = Category.CategoryId AND cll.CategoryLevel = @MaxLevel)
			
			SET @MaxLevel = @MaxLevel - 1
		END
		
	COMMIT TRANSACTION

	DROP TABLE #CategoryLevelList
END
GO




